using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;

namespace lt_pmbc_001.DAL
{
    public class AbstractDAL
    {
        /// <summary>
        /// cac bien thanh phan thuc hien thao tac truy xuat CSDL
        /// </summary>
        protected string sql;//bien giu vai tro la cau lenh truy van
        protected OleDbConnection objConnection;
        protected OleDbCommand objCommand;
        protected OleDbDataAdapter objDataAdapter;
        protected OleDbDataReader objDataReader;

        /// <summary>
        /// truyen duong dan toi thuc muc chua database de thuc thi ket noi
        /// </summary>
        /// <param name="strPath"></param>
        public AbstractDAL(String strPath)
        {
            try
            {
                string objConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + strPath;

                objConnection = new OleDbConnection(objConnectionString);
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }
        /// <summary>
        /// Add parameter cho cau lenh truy van
        /// </summary>
        /// <param name="Name"></param> 
        /// <param name="Type"></param>
        /// <param name="Size"></param>
        /// <param name="Value"></param>
        public void AddParameter(string Name, OleDbType Type, int Size, object Value)
        {
            try
            {
                objCommand.Parameters.Add(Name, Type, Size).Value = Value;

            }
            catch (System.Exception OleDbExceptionErr)
            {
                throw new System.Exception(OleDbExceptionErr.Message,
                OleDbExceptionErr.InnerException);
            }
        }
        /// <summary>
        /// mo ket noi
        /// </summary>
        public void OpenConnection()
        {
            try
            {
                objConnection.Open();
            }
            catch (OleDbException OleDbExceptionErr)
            {
                throw new Exception(OleDbExceptionErr.Message, OleDbExceptionErr.InnerException);
            }
            catch (InvalidOperationException InvalidOperationExceptionErr)
            {
                throw new Exception(InvalidOperationExceptionErr.Message, InvalidOperationExceptionErr.InnerException);
            }
        }
        /// <summary>
        /// dong ket noi
        /// </summary>
        public void CloseConnection()
        {
            objConnection.Close();
        }
        /// <summary>
        /// Khoi tao cau lenh truy van
        /// </summary>
        public void InitializeCommand()
        {
            if (objCommand == null)
            {
                try
                {
                    objCommand = new OleDbCommand(sql, objConnection);
                    //See if this is a stored procedure
                    if (!sql.ToUpper().StartsWith("SELECT ")
                        && !sql.ToUpper().StartsWith("INSERT ")
                        && !sql.ToUpper().StartsWith("UPDATE ")
                        && !sql.ToUpper().StartsWith("DELETE "))
                    {
                        objCommand.CommandType = CommandType.StoredProcedure;
                    }
                }
                catch (System.Exception e)
                {
                    throw new Exception(e.Message, e.InnerException);
                }
            }
        }
        public void InitializeDataAdapter()
        {
            try
            {
                objDataAdapter = new OleDbDataAdapter();
                objDataAdapter.SelectCommand = objCommand;//lua chon cau lenh
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }
        /// <summary>
        /// do du lieu vao dataset
        /// </summary>
        /// <param name="daoDataSet"></param>
        /// <param name="TableName"></param>
        public void FillDataSet(ref DataSet daoDataSet, String TableName)
        {
            try
            {
                InitializeCommand();
                InitializeDataAdapter();
                objDataAdapter.Fill(daoDataSet, TableName);
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
            finally
            {
                objCommand.Dispose();
                objCommand = null;
                objDataAdapter.Dispose();
                objDataAdapter = null;
            }
        }
        /// <summary>
        /// do du lieu vao dataTable
        /// </summary>
        /// <param name="daoDataSet"></param>
        /// <param name="TableName"></param>
        public void FillDataTable(ref DataTable oDataTable)
        {
            try
            {
                InitializeCommand();
                InitializeDataAdapter();
                objDataAdapter.Fill(oDataTable);
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
            finally
            {
                objCommand.Dispose();
                objCommand = null;
                objDataAdapter.Dispose();
                objDataAdapter = null;
            }
        }
        /// <summary>
        /// thuc thi cau truy van
        /// </summary>
        /// <returns>thuc thi xong, dong ket noi tra ve 1 bao thanh cong</returns>
        public Boolean ExecuteStoredProcedure()
        {
            try
            {
                OpenConnection();
                if (objCommand.ExecuteNonQuery() != 0)
                {
                    CloseConnection();
                    return true;
                }
                else
                {
                    CloseConnection();
                    return false;//that bai
                }
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public void CleanUpObject()
        {
            if (objCommand != null)
            {
                objCommand.Dispose();
                objCommand = null;
            }
            if (objDataAdapter != null)
            {
                objDataAdapter.Dispose();
                objDataAdapter = null;
            }
        }
    }
}